PostgreSQL多维空间对象 |
您所在的位置：网站首页 › cubic frost多肉 › PostgreSQL多维空间对象 |
|
CUBE是pg内置的插件，实现了一种数据类型cube来表示多维立方体。CUBE是一个多维数据类型，支持两种多维类型：多维POINT、区间（左下+右上）。以及这些几何对象的几何特性搜索和计算（方位搜索、距离计算），这些搜索都支持GiST索引。 例子： 求多维点的距离： –欧几里得距离 bill@bill=>select '(1,2,3,4)'::cube '(2,2,3,10)'::cube ; ?column? ------------------- 6.082762530298219 (1 row)–切比雪夫距离(分别每个坐标的距离，取最大值) bill@bill=>select '(1,2,3,4)'::cube '(2,2,3,10)'::cube ; ?column? ---------- 6 (1 row)–直线距离(分别每个坐标的距离，求和) bill@bill=>select '(1,2,3,4)'::cube '(2,2,3,10)'::cube ; ?column? ---------- 7 (1 row)使用cube类型进行索引查询： 对于cube类型我们可以使用gist空间索引来进行索引查询。 创建测试表： bill@bill=>create table test2 ( bill(# id int primary key, bill(# cb cube bill(# ); CREATE TABLE插入数据： bill@bill=>insert into test2 select generate_series(1,100000),cube(array[random()*10,random()*100,random()*1000],array[random()*10,random()*100,random()*1000]); INSERT 0 100000 bill@bill=>\dt+ test2 List of relations Schema | Name | Type | Owner | Size | Description --------+-------+-------+-------+---------+------------- bill | test2 | table | bill | 9120 kB | (1 row)创建gist索引： 需要注意gist索引很大，我们的表实际只有1MB不到，但是索引竟然有464MB！ bill@bill=>create index idx_t2 on test2 using gist(cb); CREATE INDEX bill@bill=>\di+ idx_t2 List of relations Schema | Name | Type | Owner | Table | Size | Description --------+--------+-------+-------+-------+--------+------------- bill | idx_t2 | index | bill | test2 | 464 MB | (1 row)包含操作的查询性能： bill@bill=>explain (analyze,verbose,timing,costs,buffers) select * from test2 where cb @> cube '[(7,67,548), (3,72,389)]'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on bill.test2 (cost=79.18..196.86 rows=100 width=60) (actual time=5.892..8.220 rows=2399 loops=1) Output: id, cb Recheck Cond: (test2.cb @> '(7, 67, 548),(3, 72, 389)'::cube) Heap Blocks: exact=1005 Buffers: shared hit=2822 -> Bitmap Index Scan on idx_t2 (cost=0.00..79.16 rows=100 width=0) (actual time=5.720..5.721 rows=2399 loops=1) Index Cond: (test2.cb @> '(7, 67, 548),(3, 72, 389)'::cube) Buffers: shared hit=1817 Planning Time: 0.303 ms Execution Time: 8.572 ms (10 rows)相交查询性能： bill@bill=>explain (analyze,verbose,timing,costs,buffers) select * from test2 where cb && cube '[(7,67,918), (9,32,399)]'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on bill.test2 (cost=391.68..857.07 rows=500 width=60) (actual time=31.379..37.760 rows=32931 loops=1) Output: id, cb Recheck Cond: (test2.cb && '(7, 67, 918),(9, 32, 399)'::cube) Heap Blocks: exact=1137 Buffers: shared hit=24210 -> Bitmap Index Scan on idx_t2 (cost=0.00..391.56 rows=500 width=0) (actual time=31.224..31.224 rows=32931 loops=1) Index Cond: (test2.cb && '(7, 67, 918),(9, 32, 399)'::cube) Buffers: shared hit=23073 Planning Time: 0.107 ms Execution Time: 39.620 ms (10 rows)包含点的查询： bill@bill=>explain (analyze,verbose,timing,costs,buffers) select * from test2 where cb @> cube '(5,23,908)'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on bill.test2 (cost=79.18..196.86 rows=100 width=60) (actual time=7.135..8.711 rows=2944 loops=1) Output: id, cb Recheck Cond: (test2.cb @> '(5, 23, 908)'::cube) Heap Blocks: exact=1044 Buffers: shared hit=4650 -> Bitmap Index Scan on idx_t2 (cost=0.00..79.16 rows=100 width=0) (actual time=6.986..6.986 rows=2944 loops=1) Index Cond: (test2.cb @> '(5, 23, 908)'::cube) Buffers: shared hit=3606 Planning Time: 0.074 ms Execution Time: 8.921 ms (10 rows)参考链接：https://www.postgresql.org/docs/12/cube.html |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |